﻿Imports System
Imports System.Xml
Imports MySql.Data
Imports MySql.Data.MySqlClient
Imports System.Text
Module Module1
    Public Path = My.Application.Info.DirectoryPath & "\"
    Public PathConfig As String = Path & "config.Xml"

    Public Hostname As String = "localhost"
    Public Username As String = "root"
    Public Password As String = ""
    Public DBName As String = "vb_librarysys"

    Private Property WHERE_Id As Integer

    Public Sub appExit()
        Application.Exit()
        Application.ExitThread()
    End Sub

    Public Sub CheckMultipleIntance()

        Process.GetProcessesByName(Process.GetCurrentProcess.ProcessName.Length)
        MessageBox.Show("Only one Intance of this program can be Open at a given time", "Duplicate instance detected", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        appExit()

    End Sub
    Public Sub createXML()
        Dim xwriter As New XmlTextWriter(PathConfig, System.Text.Encoding.UTF8)
        xwriter.WriteStartDocument(True)

        xwriter.WriteStartElement("config", "DatabaseConfig")
        xwriter.WriteElementString("hostname", "DatabaseConfig", "localhost")
        xwriter.WriteElementString("username", "DatabaseConfig", "root")
        xwriter.WriteElementString("password", "DatabaseConfig", "")
        xwriter.WriteElementString("dbname", "DatabaseConfig", "vb-librarysys")
        xwriter.WriteEndElement()
        xwriter.WriteEndDocument()
        xwriter.Close()
    End Sub
    Public Sub connectMySQL(ByVal Hostname As String, ByVal Username As String, ByVal Password As String, ByVal DBName As String)
        Dim MyConnectionString As String
        MyConnectionString = "server=" & Hostname & ";" & "uid=" & Username & ";" & "pwd=" & Password & ";" & "database=" & DBName & "; Charset = latin1;"
        Try
            Dim conn As New MySql.Data.MySqlClient.MySqlConnection(MyConnectionString)
            conn.Open()
        Catch ex As MySql.Data.MySqlClient.MySqlException
            Select Case ex.Number
                Case 0
                    MessageBox.Show("Cannot connect to server.Contact, Administrator")
                    appExit()
                Case 1045
                    MessageBox.Show("Invalid username, password, please try again")
                    appExit()
            End Select
        End Try
    End Sub
    Public Sub SelectallfromTable(ByVal table As String, ByVal Argument As String)
        Dim cs As String = "server=" & Hostname & ";" & "uid=" & Username & ";" & "pwd=" & Password & ";" & "database=" & DBName & "Charset=latin1 ;"

        Dim con As New MySqlConnection(cs)
        Try
            con.Open()
            Dim stm As String = "SELECT * FROM  " & table & " " & Argument & ""
            Dim cmd As MySqlCommand = New MySqlCommand(stm, con)
            Dim reader As MySqlDataReader = cmd.ExecuteReader()
            Dim temp_id As String, temp_username As String, temp_password As String

            While reader.Read()

                temp_id = reader.GetInt32("id")
                temp_username = reader.GetString("username")
                temp_password = reader.GetString("password")
                MsgBox(temp_id & ";" & temp_username & ";" & temp_password & vbCr)
            End While

            reader.Close()
        Catch ex As MySqlException
            MsgBox("Error:" & ex.ToString())
        Finally
            con.Close()
        End Try
    End Sub
    Public Sub InsertIntoTable(ByVal table As String)
        Dim cs As String = "server=" & Hostname & ";" & "uid=" & Username & ";" & "pwd=" & Password & ";" & "database=" & DBName & ";Charset=latin1;"
        Dim con As New MySqlConnection(cs)
        Dim cmd As New MySqlCommand
        Try
            con.Open()
            cmd.Connection = con

            cmd.CommandText = "INSERT INTO" & table & "(username, password) VALUES(@username, @password)"

            cmd.Prepare()

            cmd.Parameters.AddWithValue("@username", "usernamenimarlon")
            cmd.Parameters.AddWithValue("@password", "passwordnimarlon")
            cmd.ExecuteNonQuery()

            con.Close()
        Catch ex As MySqlException
            MsgBox("Error:" & ex.ToString())
        End Try
    End Sub
    Public Sub DeleteIntoTableWhere(ByVal table As String, ByVal uid As Integer)
        Dim cs As String = "server=" & Hostname & ";" & "uid=" & Username & ";" & "pwd=" & Password & ";" & "database=" & ";" & DBName & "; Charset=latin1;"
        Dim conn As New MySqlConnection(cs)
        Dim cmd As New MySqlCommand()
        Try
            conn.Open()
            cmd.Connection = conn

            cmd.CommandText = "DELETE FROM" & table & "WHERE id= " & uid & ""

            cmd.ExecuteNonQuery()

            conn.Close()

        Catch ex As MySqlException
            MsgBox("Error:" & ex.ToString())
        End Try
    End Sub
    Public Sub UpdateIntoTableWhere(ByVal table As String, ByVal FieldName As String, ByVal Value As String, ByVal uid As Integer)
        Dim cs As String = "server=" & Hostname & ";" & "uid=" & Username & ";" & "pwd=" & Password & ";" & "database=" & DBName & "; Charset=latin1;"

        Dim con As New MySqlConnection(cs)
        Dim cmd As New MySqlCommand()

        Try
            con.Open()

            cmd.Connection = con

            cmd.CommandText = "UPDATE" & table & "SET" & FieldName & "='" & Value & "' WHERE id= " & uid & ""
            cmd.ExecuteNonQuery()
            'cmd.CommandText="UPDATEBooksSETTitle='War and Peace'
            WHERE_Id = "1"
            cmd.ExecuteNonQuery()
            'cmd.CommandText="UPDATEBooksSETTitle='Anna Karenina'
            WHERE_Id = "2"
            cmd.ExecuteNonQuery()
            con.Close()
        Catch ex As MySqlException
            MsgBox("Error:" & ex.ToString())
        End Try
    End Sub
    Public Sub XMLtoMySQL()
        If My.Computer.FileSystem.FileExists(PathConfig) Then
            Try
                Dim strHostname, strUsername, strPassword, strDBName, strAppName As String

                Dim xreader As XmlTextReader = Nothing
                xreader = New XmlTextReader(PathConfig)
                While (xreader.Read())
                    Dim type = xreader.NodeType
                    If (type = XmlNodeType.Element) Then

                    End If
                    If (xreader.Name = "hostname") Then
                        strHostname =
                            xreader.ReadInnerXml.ToString()
                    ElseIf (xreader.Name = "username") Then
                        strUsername =
                            xreader.ReadInnerXml.ToString()
                    ElseIf (xreader.Name = "password") Then
                        strPassword =
                            xreader.ReadInnerXml.ToString()
                    ElseIf (xreader.Name = "dbname") Then
                        strDBName =
                            (-xreader.ReadInnerXml.ToString())
                    ElseIf (xreader.Name = "appname") Then
                        strAppName =
                            xreader.ReadInnerXml.ToString()
                    End If
                End While
                connectMySQL("Localhost", "root", "", "vb-librarysys")
            Catch ex As Exception
                MsgBox(Err.Description)
            End Try
        Else
            'if config.xml not exist it will create one with default values createXML()
        End If
    End Sub

 
End Module
